﻿using System;
using System.IO;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace DBTool
{
    public class DBConnectHandler : Singleton< DBConnectHandler >
    {
        #region Member Variables
        private string _null = "null";
        #endregion


        #region CSV_INSERT_DB
        public bool TableInsertIntoDBProcess(  string tablePath )
        {
            string tableName = Path.GetFileNameWithoutExtension( tablePath );

            DBConnect.Instance.DeleteTable( tableName );

            bool result = InsertIntoDB( tablePath );

            return result;
        }

        private bool InsertIntoDB( string tablePath )
        {
            CSVReader tableCSV = new CSVReader(tablePath);

            // GetCSV Titles
            string[] tableTitles = tableCSV.GetColumnTitle();

            if (null == tableTitles)
            {
                MessageBox.Show("InsertDB DB Error : " + tablePath, "Not Exist Titles", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }

            string dbColumns = "";

            foreach (string tableColumn in tableTitles)
            {
                dbColumns += "`";
                dbColumns += tableColumn;
                dbColumns += "`";
                dbColumns += ",";
            }

            dbColumns = dbColumns.TrimEnd(',');
            //

            // Get Row And Insert
            for (int rowCount = 1; rowCount < tableCSV.GetRowCount(); ++rowCount)
            {
                string insertValue = "";

                foreach (string columnName in tableTitles)
                {
                    if( _null != tableCSV.Find( rowCount, columnName ) )
                        insertValue += "'";
                        
                    insertValue += tableCSV.Find( rowCount, columnName );

                    if ( _null != tableCSV.Find( rowCount, columnName ) )
                        insertValue += "'";

                    insertValue += ",";
                }

                insertValue = insertValue.TrimEnd(',');

                string tableName = Path.GetFileNameWithoutExtension(tablePath);

                string query = "INSERT INTO " + tableName + "(" + dbColumns + ")" +
                               " VALUES(" + insertValue + ")";

                bool insertReslt = DBConnect.Instance.Insert(query, tableName);

                if (false == insertReslt)
                    return false;
            }

            return true;
        }
        #endregion
    }
}
